package com.revenco.bi.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.revenco.bi.dao.ChartSqlDao;
import com.revenco.bi.dao.TnPublicDemensionValueSelDao;
import com.revenco.bi.model.TnPublicDemensionValueSel;
import com.revenco.bi.model.TnTableInfo;
import com.revenco.bi.model.userbean.AnychartDataBean;
import com.revenco.bi.model.userbean.CFieldsBean;
import com.revenco.bi.model.userbean.CQueryBean;
import com.revenco.bi.model.userbean.ShowSql;
import com.revenco.bi.model.userbean.XFieldsBean;
import com.revenco.bi.model.userbean.YFieldsBean;
import com.revenco.bi.model.userbean.YQueryBean;
import com.revenco.bi.service.ChartSqlPieceService;
import com.revenco.bi.service.TnColumnInfoService;
import com.revenco.bi.service.TnOthtableMaintableRelaService;
import com.revenco.bi.util.Bean2Json;
import com.revenco.bi.util.JsonBinder;
import com.revenco.bi.util.SqlTemplate;

/**
 * 
 * @author zhaozz
 * 
 */
@Service
public class ChartSqlPieceServiceImpl implements ChartSqlPieceService {
	@Autowired
	private ChartSqlDao chartSqlDao;
	@Autowired
	private TnColumnInfoService tnService;
	@Autowired
	private TnOthtableMaintableRelaService tnOthtableMaintableRelaService;
	@Autowired
	private TnPublicDemensionValueSelDao tnPublicDemensionValueSelDao;

	private JsonBinder binder = new JsonBinder();
	private ShowSql showSql = new ShowSql();
	private Bean2Json bean2Json = new Bean2Json();
	private SqlTemplate sqlTemplate;
	private List<String> legendList = null;

	/**
	 * 根据筛选条件、X轴字段、Y轴字段拼接sql 返回 Sql语句
	 */

	public String spliceSql(String CFields, String XFields, String YFields, String mulRelationData) {
		String fullSql = "";

		this.getSql(CFields, XFields, YFields, mulRelationData);

		fullSql = bean2Json.sqlJson(showSql);
		return fullSql;
	}

	/**
	 * @author LingJun
	 * @param CFields
	 * @param XFields
	 * @param YFields
	 * @return
	 */
	public ShowSql getSql(String CFields, String XFields, String YFields, String mulRelationData) {
		try {
			// 序列化
			XFieldsBean xFields = binder.fromJson(XFields, XFieldsBean.class);

			CQueryBean cFields = null;
			if (CFields.length() > 5) {
				cFields = binder.fromJson(CFields, CQueryBean.class);
			}
			YQueryBean yFields = binder.fromJson(YFields, YQueryBean.class);
			String sql = getPieceSql(cFields.getCONDITION(), xFields,
					yFields.getFullSelect(), mulRelationData);
			String testResult = chartSqlDao.sqlTest(sql);
			showSql.setSql(sql);
			// 执行sql进行验证
			if ("true".equals(testResult)) {
				showSql.setSuccess(true);
				showSql.setMsg("Exe Success!");
			} else {
				showSql.setSuccess(false);
				showSql.setMsg(testResult);
			}
		} catch (Exception e) {
			showSql.setSuccess(false);
			showSql.setMsg(e.toString());
			System.out.println(e);
		}
		return showSql;
	}

	/**
	 * 通过数据库中保存的三张表信息获取拼接sql需要的信息
	 * 
	 * @param tnChartBaseinfo
	 * @param tnChartDataitemsList
	 * @param tnChartParamsList
	 * @return select X字段，FUN(Y字段) from 相关EDS数据表 join 相关REF维度表 on 关联字段 where
	 *         筛选条件 GOURPY BY X字段 ORDER BY 排序字段
	 */
	public String getPieceSql(CFieldsBean[] cfieldsArray, XFieldsBean xFields,
			YFieldsBean[] yField, String mulRelationData) {
		sqlTemplate = new SqlTemplate();
		// 获取维度值
		List<TnPublicDemensionValueSel> demenValueList = tnOthtableMaintableRelaService
				.getTnPublicDemensionValueSelByPvId(xFields.getColumnId());// column_4
		String sql = "";
		String fromTable = getFromTable(yField);// 相关EDS数据表
		legendList = new ArrayList<String>();
		Map<String, String> map = new HashMap<String, String>();
		for (int i = 0; i < yField.length; i++) {// 添加y轴统计字段集合
			String yFieldCd = yField[i].getName();
			legendList.add(yFieldCd);
			map.put(yField[i].getTableName(), getAsNameByName(yField[i].getTableName()));
		}
		map.put(fromTable, getAsNameByName(fromTable));
		String mulRelaTable = "";
		String relation = "";
		if (!mulRelationData.equals("{}")) { 
			mulRelaTable = mulRelaTable(fromTable, mulRelationData);
			if (!"".equals(mulRelaTable)) {
				map.put(mulRelaTable, getAsNameByName(mulRelaTable));
				relation = addPoind(fromTable, mulRelationData);
			}
		}
		if (xFields.getTableName() != null) {
			map.put(xFields.getTableName(), getAsNameByName(xFields.getTableName())); // x轴表名和别名
		}
		if (xFields.getName() != null && !"0".equals(xFields.getName())) {
			String colType = xFields.getColType();// 应该从数据库获取
			Map<String, String> xFieldMap = getXField(xFields, demenValueList,
					colType);// X字段
			String relaField = getRelaField(xFields);// 关联字段
			String joinTable = getJoinTable(colType, demenValueList);// 相关REF维度表
			// joinTable为"(子查询)",设置别名为"dmt"
			if (joinTable.indexOf("from") != -1) {
				map.put(joinTable, "dmt");
			} else {
				String joinTableName = joinTable.substring(2, joinTable.length() - 2);
				// joinTable为"(表名)"
				map.put(joinTable, getAsNameByName(joinTableName));
			}
			sql = sqlTemplate.getSqlDetail(xFieldMap, yField, fromTable,
					joinTable, relaField, cfieldsArray, mulRelationData, relation, mulRelaTable, map, xFields.getTableName());
		} else {
			sql = sqlTemplate.getSqlDetail4Oil(yField, cfieldsArray, fromTable, map, mulRelaTable, relation);
		}
		return sql;
	}

	/**
	 * 获取横坐标的查询字段
	 * 
	 * @param xFields
	 * @return
	 */
	private String getRelaField(XFieldsBean xFields) {
		String selaField = xFields.getName();
		return selaField;
	}

	/**
	 * 获取连接表的查询
	 * 
	 * @param colType
	 * @param demenValueList
	 * @return
	 */
	private String getJoinTable(String colType,
			List<TnPublicDemensionValueSel> demenValueList) {
		String JoinTable = "";
		// 字段类型是否是维
		if ("1".equals(colType) && demenValueList != null
				&& demenValueList.size() > 0) {
			JoinTable = " (" + demenValueList.get(0).getConfigUrl() + ") ";

		} else {

		}
		return JoinTable;
	}

	/**
	 * 获取查询的数据表名
	 * 
	 * @param yField
	 * @return
	 */
	private String getFromTable(YFieldsBean[] yField) {
		String yFieldTableName = yField[0].getTableName();
		return yFieldTableName;
	}

	/**
	 * 得到统计x轴字段 和别名的集合
	 * 
	 * @param xFields
	 * @param demenValueList
	 * @param colType
	 * @return
	 */
	private Map<String, String> getXField(XFieldsBean xFields,
			List<TnPublicDemensionValueSel> demenValueList, String colType) {
		Map<String, String> xFieldCdMap = new HashMap<String, String>();
		if (xFields == null || xFields.getId() == null) {
			return null;
		}
		String name = "", id = "";
		// 字段类型是否是维
		if ("1".equals(colType) && demenValueList != null
				&& demenValueList.size() > 0) {
			// 维度字段属性别名分别为 name 和 id
			name = demenValueList.get(0).getTextColName();
			id = demenValueList.get(0).getValueColName();

		} else {
			// EDS相关数据表

			id = name = xFields.getName();

		}
		xFieldCdMap.put("name", name);
		xFieldCdMap.put("id", id);
		return xFieldCdMap;
	}

	/**
	 * 由x、y以及查询条件得到统计的数据
	 */
	public Map<String, List<AnychartDataBean>> getSeriesData(String CFields,
			String XFields, String YFields, String mulRelationData) {
		String sql = this.getSql(CFields, XFields, YFields, mulRelationData).getSql();
		return chartSqlDao.getSeriesData(sql, legendList);
	}

	public Map<String, Map<String, String>> getSeriesData2(String CFields,
			String XFields, String YFields, String mulRelationData) {
		String sql = this.getSql(CFields, XFields, YFields, mulRelationData).getSql();
		return chartSqlDao.getSeriesData2(sql, legendList);
	}

	/**
	 * 跟y轴相关数据，获取油量表中最大，最小字段值
	 */
	public String getOilGaugeBeanSql(String YFields, String mulRelationData) {
		String sql = "";
		ArrayList<String> yFieldArray = new ArrayList<String>();
		YQueryBean yFields = binder.fromJson(YFields, YQueryBean.class);
		SqlTemplate sqlTemplate = new SqlTemplate();
		for (int i = 0; i < yFields.getFullSelect().length; i++) {
			YFieldsBean yb = yFields.getFullSelect()[i];
			if (yb.getCustomStle() != null && !"".equals(yb.getCustomStle())) {
				// 自定义统计
				String fixStr= sqlTemplate.splitString(yb.getCustomStle());
				yFieldArray.add(fixStr);
			} else {
				yFieldArray.add(yb.getFun() + "(" + getAsNameByName(yb.getTableName()) + "." + yb.getName() + ") ");
			}
		}
		Iterator<String> it = yFieldArray.iterator();
		String yfStr = "";
		while (it.hasNext()) {
			yfStr += it.next();
			if (it.hasNext()) {
				yfStr += " , ";
			}
		}
		String fromTable = yFields.getFullSelect()[0].getTableName();
		String joinTable = "";
		for (int i = 0; i < yFields.getFullSelect().length; i++) {
			YFieldsBean yb = yFields.getFullSelect()[i];
			if (!yb.getTableName().equals(fromTable)) {
				joinTable = yb.getTableName();
			}
		}
		// 关联关系
		String relation = "";
		if (!mulRelationData.equals("{}")) {
			relation = addPoind(fromTable, mulRelationData);
		}
		sql = "SELECT greatest(" + yfStr + ") as maxCount, least(" + yfStr
				+ ") as minCount from " + fromTable + " " + getAsNameByName(fromTable);
		if (!"".equals(joinTable)) {
			sql += " join " + joinTable + " " + getAsNameByName(joinTable) + " on " + relation;
		}
		return sql;
	}
	
	/**
	 * 解析多表关联中的表名
	 * @param fromTable
	 * @param mulRelationData
	 * @return table
	 */
	private String mulRelaTable(String fromTable, String mulRelationData) {
		String multiRealTable = "";
		if (!mulRelationData.equals("{}")) {
			JSONObject rootObj;
			rootObj = new JSONObject(mulRelationData);
			JSONArray array = rootObj.getJSONArray("COLUMN");
			for (int i = 0; i < array.length(); i++) {
				JSONObject temp = array.getJSONObject(i);
				String leftTable = getTable((String) temp.get("first_Column")); // 前台传入的是table_id
				String rightTable = getTable((String) temp.get("last_Column"));
				// 连接的是哪张表
				if (fromTable.equals(getTableById(leftTable))) {
					multiRealTable = getTableById(rightTable);
				} else {
					multiRealTable = getTableById(leftTable);
				}
			} 
		}
		return multiRealTable;
	}
	
	/**
	 * 获取多表关联中的表名
	 * @param relaColumn(设置多表关联时后台传递的first_Column或last_Column)
	 * @return table
	 */
	public String getTable(String relaColumn) {
		String table = relaColumn.split("=")[0];
		return table;
	}
	
	/**
	 * 获取多表关联中的表字段
	 * @param relaColumn(设置多表关联时后台传递的first_Column或last_Column)
	 * @return column
	 */
	public String getColumn(String relaColumn) {
		return relaColumn.split("=")[1];
	}
	
	/**
	 * 根据table_id查询表名
	 */
	public String getTableById(String id) {
		return chartSqlDao.getTableById(id).getName();
	}
	
	/**
	 * 根据table_id查询别名
	 */
	public String getAsNameById(String id) {
		return chartSqlDao.getTableById(id).getAsName();
	}
	
	/**
	 * 根据表名查询别名
	 */
	public String getAsNameByName(String name) {
		return chartSqlDao.getAsNameByName(name);
	}
	
	/**
	 * 如果设置了关联关系，判断是关联哪张表的哪个字段
	 * 
	 * @param tableAlias
	 * @param relaField
	 * @param mulRelationData
	 * @return sql
	 */
	private String addPoind(String tableAlias, String mulRelationData) {
		String sql = "";
		if (!mulRelationData.equals("{}")) {
			StringBuffer buff = new StringBuffer();
			JSONObject rootObj;
			try {
				rootObj = new JSONObject(mulRelationData);
				JSONArray array = rootObj.getJSONArray("COLUMN");
				for (int i = 0; i < array.length(); i++) {
					JSONObject temp = array.getJSONObject(i);
					String leftTable = getTable((String) temp.get("first_Column"));
					buff.append(getAsNameById(leftTable));
					buff.append(".");
					String leftColumn = getColumn((String) temp.get("first_Column"));
					
					buff.append(tnService.getNameById(leftColumn).getName() + " = ");
					String rightTable = getTable((String) temp.get("last_Column"));
					buff.append(getAsNameById(rightTable));
					String rightColumn = getColumn((String) temp.get("last_Column"));
					buff.append(".");
					buff.append(tnService.getNameById(rightColumn).getName() + " and ");
				} 
				sql = buff.toString();
				sql = sql.substring(0, sql.length()-5);
				System.out.println("关联关系：" + sql);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return sql;
	}
	

}
